﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.OleDb;
using System.Data;
using TimKiem.DTO;
using TimKiem.DTO.TimKiem;
namespace TimKiem.DataLayer.TimKiem
{
    public class TimKiemRepository
    {
        private static OleDbConnection con;
        private static OleDbDataAdapter dAdapter;
        
        private static string conString = ConfigurationManager.AppSettings["ConnectionString"];
        public static DataSet GetAllKeyword()
        {
            DataSet dSet = new DataSet();
            try
            {
                con = new OleDbConnection(conString);
                dAdapter = new OleDbDataAdapter(@"SELECT keyword
                                                  FROM (SELECT        TuKhoaTenDiaDiem AS keyword
                                                          FROM            TUKHOADIADIEM dd
                                                          UNION
                                                          SELECT        TuKhoaDichVu AS keyword
                                                          FROM            TUKHOADICHVU dv
                                                          UNION
                                                          SELECT        TuKhoaDuong AS keyword
                                                          FROM            TUKHOADUONG d
                                                          UNION
                                                          SELECT        TuKhoaPhuong AS keyword
                                                          FROM            TUKHOAPHUONG p
                                                          UNION
                                                          SELECT        TuKhoaQuanHuyen AS keyword
                                                          FROM            TUKHOAQUANHUYEN q
                                                          UNION
                                                          SELECT        TuKhoaTinhThanh AS keyword
                                                          FROM            TUKHOATINHTHANH tp
                                                        ) ALLKeyWord"
                                                , con);
               
                dAdapter.Fill(dSet, "AllKeyword");
            }
            catch
            {

            }


            return dSet;
        }
        public static DataSet GetAll()
        {
            con = new OleDbConnection(conString);
            string sql = @"SELECT DISTINCT 
                         dl.ChuThich, dl.KinhDo, dl.MaDuLieu, 
                         dv.TenDichVu + '  ' + dd.TenDiaDiem + '  ' + dl.SoNha + '  ' + d.TenDuong + '  ' + p.TenPhuong + '  ' + q.TenQuanHuyen + '  ' + tp.TenTinhThanh AS DiaChi, 
                         dl.ViDo
                         FROM            ((((((DULIEU dl INNER JOIN
                                                 DICHVU dv ON dl.MaDichVu = dv.MaDichVu) INNER JOIN
                                                 DUONG d ON dl.MaDuong = d.MaDuong) INNER JOIN
                                                 PHUONG p ON dl.MaPhuong = p.MaPhuong) INNER JOIN
                                                 QUANHUYEN q ON dl.MaQuanHuyen = q.MaQuanHuyen) INNER JOIN
                                                 TINHTHANH tp ON dl.MaTinhThanh = tp.MaTinhThanh) INNER JOIN
                                                 TENDIADIEM dd ON dl.MaTenDiaDiem = dd.MaTenDiaDiem)
                            ";
            dAdapter = new OleDbDataAdapter(sql, con);
            DataSet dSet = new DataSet();
            dAdapter.Fill(dSet, "DiaDiem");
            return dSet;
        }
        public static DataSet TimKiem(string keyword)
        {
            con = new OleDbConnection(conString);
            string sql = @"SELECT DISTINCT 
                         DuLieuTimTheoTuKhoa.ChuThich
                        , DuLieuTimTheoTuKhoa.KinhDo
                        , DuLieuTimTheoTuKhoa.MaDuLieu 
                        , dv.TenDichVu + '  ' + dd.TenDiaDiem + '  ' + DuLieuTimTheoTuKhoa.SoNha + '  ' + d.TenDuong + '  ' + p.TenPhuong + '  ' + q.TenQuanHuyen + '  ' + tp.TenTinhThanh AS DiaChi
                        , DuLieuTimTheoTuKhoa.ViDo
                        FROM            (((((((SELECT DISTINCT 
                                                            dl.ChuThich
                                                            , dl.KinhDo
                                                            , dl.MaDichVu
                                                            , dl.MaDuLieu
                                                            , dl.MaDuong
                                                            , dl.MaPhuong
                                                            , dl.MaQuanHuyen
                                                            , dl.MaTenDiaDiem
                                                            , dl.MaTinhThanh
                                                            , dl.SoNha 
                                                            , dl.ViDo
                                                FROM (DULIEU dl INNER JOIN
                                                                (SELECT DISTINCT MaTenDiaDiem
                                                                  FROM            TUKHOADIADIEM
                                                                  WHERE        (TuKhoaTenDiaDiem LIKE '"+keyword+@"%')) dd_1 ON dl.MaTenDiaDiem = dd_1.MaTenDiaDiem)
                                  UNION
                                  SELECT DISTINCT 
                                                           dl.ChuThich, dl.KinhDo, dl.MaDichVu, dl.MaDuLieu, dl.MaDuong, dl.MaPhuong, dl.MaQuanHuyen, dl.MaTenDiaDiem, dl.MaTinhThanh, dl.SoNha, 
                                                           dl.ViDo
                                  FROM            (DULIEU dl INNER JOIN
                                                               (SELECT DISTINCT MaDichVu
                                                                 FROM            TUKHOADICHVU
                                                                 WHERE        (TuKhoaDichVu LIKE '" + keyword + @"%')) dv_1 ON dl.MaDichVu = dv_1.MaDichVu)
                                  UNION
                                  SELECT DISTINCT 
                                                           dl.ChuThich, dl.KinhDo, dl.MaDichVu, dl.MaDuLieu, dl.MaDuong, dl.MaPhuong, dl.MaQuanHuyen, dl.MaTenDiaDiem, dl.MaTinhThanh, dl.SoNha, 
                                                           dl.ViDo
                                  FROM            (DULIEU dl INNER JOIN
                                                               (SELECT DISTINCT MaDuong
                                                                 FROM            TUKHOADUONG
                                                                 WHERE        (TuKhoaDuong LIKE '" + keyword + @"%')) d_1 ON dl.MaDuong = d_1.MaDuong)
                                  UNION
                                  SELECT DISTINCT 
                                                           dl.ChuThich, dl.KinhDo, dl.MaDichVu, dl.MaDuLieu, dl.MaDuong, dl.MaPhuong, dl.MaQuanHuyen, dl.MaTenDiaDiem, dl.MaTinhThanh, dl.SoNha, 
                                                           dl.ViDo
                                  FROM            (DULIEU dl INNER JOIN
                                                               (SELECT DISTINCT MaPhuong
                                                                 FROM            TUKHOAPHUONG
                                                                 WHERE        (TuKhoaPhuong LIKE '" + keyword + @"%')) p_1 ON dl.MaPhuong = p_1.MaPhuong)
                                  UNION
                                  SELECT DISTINCT 
                                                           dl.ChuThich, dl.KinhDo, dl.MaDichVu, dl.MaDuLieu, dl.MaDuong, dl.MaPhuong, dl.MaQuanHuyen, dl.MaTenDiaDiem, dl.MaTinhThanh, dl.SoNha, 
                                                           dl.ViDo
                                  FROM            (DULIEU dl INNER JOIN
                                                               (SELECT DISTINCT MaQuanHuyen
                                                                 FROM            TUKHOAQUANHUYEN
                                                                 WHERE        (TuKhoaQuanHuyen LIKE '" + keyword + @"%')) q_1 ON dl.MaQuanHuyen = q_1.MaQuanHuyen)
                                  UNION
                                  SELECT DISTINCT 
                                                           dl.ChuThich, dl.KinhDo, dl.MaDichVu, dl.MaDuLieu, dl.MaDuong, dl.MaPhuong, dl.MaQuanHuyen, dl.MaTenDiaDiem, dl.MaTinhThanh, dl.SoNha, 
                                                           dl.ViDo
                                  FROM            (DULIEU dl INNER JOIN
                                                               (SELECT DISTINCT MaTinhThanh
                                                                 FROM            TUKHOATINHTHANH
                                                                 WHERE        (TuKhoaTinhThanh LIKE '" + keyword + @"%')) tp_1 ON dl.MaTinhThanh = tp_1.MaTinhThanh)) DuLieuTimTheoTuKhoa INNER JOIN
                                 DICHVU dv ON DuLieuTimTheoTuKhoa.MaDichVu = dv.MaDichVu) INNER JOIN
                                 DUONG d ON DuLieuTimTheoTuKhoa.MaDuong = d.MaDuong) INNER JOIN
                                 PHUONG p ON DuLieuTimTheoTuKhoa.MaPhuong = p.MaPhuong) INNER JOIN
                                 QUANHUYEN q ON DuLieuTimTheoTuKhoa.MaQuanHuyen = q.MaQuanHuyen) INNER JOIN
                                 TINHTHANH tp ON DuLieuTimTheoTuKhoa.MaTinhThanh = tp.MaTinhThanh) INNER JOIN
                                 TENDIADIEM dd ON DuLieuTimTheoTuKhoa.MaTenDiaDiem = dd.MaTenDiaDiem)
                            ";
            dAdapter = new OleDbDataAdapter(sql, con);
            DataSet dSet = new DataSet();
            dAdapter.Fill(dSet, "Results");
            return dSet;
        }
        public static DataSet SearchLike(string keyword)
        {
            con = new OleDbConnection(conString);
            string sql = @"SELECT        ChuThich, KinhDo, MaDuLieu, DiaChi,ViDo
                            FROM            (SELECT        dl.ChuThich, dl.KinhDo, dl.MaDuLieu 
                                                            , REPLACE(dv.TenDichVu, ' ', '') + '' + REPLACE(dd.TenDiaDiem, ' ', '') + '' + dl.SoNha + '' + REPLACE(d.TenDuong,' ', '') + '' + REPLACE(p.TenPhuong, ' ', '') + '' + REPLACE(q.TenQuanHuyen, ' ', '') + '' + REPLACE(tp.TenTinhThanh, ' ', '') AS tempDc
                                                            , dv.TenDichVu + '  ' + dd.TenDiaDiem + '  ' + dl.SoNha + '  ' + d.TenDuong + '  ' + p.TenPhuong + '  ' + q.TenQuanHuyen + '  ' + tp.TenTinhThanh AS DiaChi
                                                            , dl.ViDo
                                              FROM            ((((((DULIEU dl INNER JOIN
                                                                        DICHVU dv ON dl.MaDichVu = dv.MaDichVu) INNER JOIN
                                                                        DUONG d ON dl.MaDuong = d.MaDuong) INNER JOIN
                                                                        PHUONG p ON dl.MaPhuong = p.MaPhuong) INNER JOIN
                                                                        QUANHUYEN q ON dl.MaQuanHuyen = q.MaQuanHuyen) INNER JOIN
                                                                        TINHTHANH tp ON dl.MaTinhThanh = tp.MaTinhThanh) INNER JOIN
                                                                        TENDIADIEM dd ON dl.MaTenDiaDiem = dd.MaTenDiaDiem)) Results
                            WHERE        (tempDc LIKE '%" + keyword + "%')";
            dAdapter = new OleDbDataAdapter(sql, con);
            DataSet dSet = new DataSet();
            dAdapter.Fill(dSet, "Results");
            return dSet;
        }
    }
}
